package yhao.demo.autocode.core.sqlmapper.connect;

import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbConnectionUtil {
    public static SchemeInfo getAllSchemeInfo(
            DbConnectParam dbParam,String dbName,String tableName) throws Exception {
        System.out.println("开始获得数据库连接...");
        Connection conn = getJdbcConnect(dbParam.getDbType(),dbParam.getUsername(), dbParam.getPassword(), dbParam.getUrl());
        if (conn == null) {
            throw new RuntimeException("获得数据库连接失败");
        }
        System.out.println("获得数据库连接...DONE");

        System.out.println("开始获得表结构信息...");
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SchemeInfo info = new SchemeInfo();
        List<SqlModelDef> defs = new ArrayList<>();
        info.setDefList(defs);
        // 第三步，创建PreparedStatement
        if(dbParam.getDbType().equals(DbType.MYSQL)){
            String sql = String.format("select TABLE_COMMENT from information_schema.TABLES " +
                    "where TABLE_SCHEMA='%s' and table_name='%s'", dbName, tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                info.setTableDesc(rs.getString("TABLE_COMMENT"));
            }

            sql = String.format("select * from information_schema.columns " +
                    "where TABLE_SCHEMA='%s' and table_name='%s' order by ordinal_position", dbName, tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SqlModelDef def = new SqlModelDef();
                def.setDataType(rs.getString("DATA_TYPE"));
                def.setColumnName(rs.getString("COLUMN_NAME"));
                def.setPrimaryKey("PRI".equals(rs.getString("COLUMN_KEY")));
                def.setColumnComment(rs.getString("COLUMN_COMMENT"));
                def.setCanNull("YES".equals(rs.getString("IS_NULLABLE")));
                def.setColumnType(rs.getString("COLUMN_TYPE"));
                def.setIndexKey(StringUtils.isNotBlank(rs.getString("COLUMN_KEY")));
                defs.add(def);
            }
        }else if(dbParam.getDbType().equals(DbType.ORACLE)){
            String sql = String.format("select COMMENTS from user_tab_comments where table_name='%s' ", tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                info.setTableDesc(rs.getString("COMMENTS"));
            }
            sql = String.format("select utc.DATA_LENGTH,utc.DATA_PRECISION,utc.DATA_SCALE,utc.NULLABLE,utc.DATA_TYPE,utc.COLUMN_NAME," +
                    "utc.COLUMN_ID,ucc.COMMENTS " +
                    "from user_tab_columns utc\n" +
                    "left join user_col_comments ucc on utc.table_name=ucc.table_name and utc.column_name = ucc.column_name\n" +
                    "where utc.table_name='%s' order by utc.COLUMN_ID", tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            Map<String,SqlModelDef> middle = new HashMap<>();
            while (rs.next()) {
                SqlModelDef def = new SqlModelDef();
                def.setDataType(rs.getString("DATA_TYPE"));
                def.setColumnName(rs.getString("COLUMN_NAME"));
                def.setColumnComment(rs.getString("COMMENTS"));

                String dataLength = rs.getString("DATA_LENGTH");
                String dataPrecision = rs.getString("DATA_PRECISION");
                String dataScale = rs.getString("DATA_SCALE");

                if(StringUtils.isNotEmpty(dataPrecision)){
                    def.setColumnType(def.getDataType()+"("+dataPrecision+","+dataScale+")");
                }else{
                    def.setColumnType(def.getDataType()+"("+dataLength+")");
                }
                def.setCanNull("Y".equals(rs.getString("NULLABLE")));

                defs.add(def);
                middle.put(def.getColumnName(),def);
            }
            sql = String.format("select t.*,i.index_type from user_ind_columns t,user_indexes i \n" +
                    "where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '%s'",tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String column = rs.getString("COLUMN_NAME");
                SqlModelDef def = middle.get(column);
                if(def!=null){
                    def.setIndexKey(true);
                }
            }
            sql = String.format("select cu.* from user_cons_columns cu, user_constraints au \n" +
                    "where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '%s'",tableName);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String column = rs.getString("COLUMN_NAME");
                SqlModelDef def = middle.get(column);
                if(def!=null){
                    def.setPrimaryKey(true);
                }
            }
        }

        closeJdbcStatement(pstmt, rs);
        System.out.println("获得表结构信息...DONE");

        System.out.println("关闭数据库连接...");
        closeConnect(conn);
        System.out.println("关闭数据库连接...DONE");
        System.out.println();

        return info;
    }


    private static Connection getJdbcConnect(DbType dbType,String username, String password, String url) {
        Connection conn = null;
        try {
            // 第一步，加载数据库驱动
            Class.forName(dbType.getDriver());
            // 第二步，取得数据库连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }



    private static void closeJdbcStatement(PreparedStatement pstmt, ResultSet rs) {
        // 遵循从里到外关闭的原则
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
            }
        }
    }

    private static void closeConnect(Connection conn) {
        // 必须关闭Connection
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
}
